package framework.database;

import javax.sql.DataSource;
import javax.naming.InitialContext;
import javax.naming.NamingException;
import java.sql.DriverManager;
import java.sql.Connection;
import java.sql.Statement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.Vector;
import java.util.Hashtable;

public class DBAccess {

    private DataSource dataSource;

    private String driver;

    private String url;

    private String user;

    private String password;

    private boolean isDirect;

    private final static int DEBUG = 1;

    private final static int INFO = 2;

    private final static int ERROR = 3;

    private static org.apache.log4j.Category log4j = org.apache.log4j.Category.getInstance(DBAccess.class.getName());
    
    private boolean isLog4jLogging = true;
    
    private Vector log;
    
    /**
     * log Vector initialisation
     */
    public void initLog() {
        log = new Vector();
    }
    
    /**
     * log Vector getter
     */
    public Vector getLog() {
        return log;
    }
    
    /**
     * setting logging system
     */
    private void setLoggingSystem(boolean isLog4jLogging) {
        this.isLog4jLogging = isLog4jLogging;
        if (isLog4jLogging)
            log = null;
        else
            initLog(); 
    }
    
    /**
     * centralised logging system
     */
    private void addLog(int logType, String logString) {
        if (isLog4jLogging) {
            if (logType == DEBUG)
                log4j.debug(logString);
            else if (logType == INFO)
                log4j.info(logString);
            else if (logType == ERROR)
                log4j.error(logString);
        } else {
            if (log != null && logType == ERROR)
                log.addElement(logString);
        }
    }

    /**
     * constructor : datasource initialisation
     */
    public DBAccess(String source) {
        dataSource = null;
        if (source == null) {
            addLog(ERROR, "datasource initialisation error : source null");
        } else {
            addLog(INFO, "datasource initialisation : source=" + source);
            try {
                dataSource = (DataSource) new InitialContext().lookup(source);
                isDirect = false;
            } catch (NamingException ne) {
                addLog(ERROR, "datasource initialisation error : source=" + source + " *** message=" + ne.getMessage());
            }
        }
    }

    /**
     * constructor : datasource initialisation & logging system initialisation
     */
    public DBAccess(String source, boolean isLog4jLogging) {
        setLoggingSystem(isLog4jLogging);
        dataSource = null;
        if (source == null) {
            addLog(ERROR, "datasource initialisation error : source null");
        } else {
            addLog(INFO, "datasource initialisation : source=" + source);
            try {
                dataSource = (DataSource) new InitialContext().lookup(source);
                isDirect = false;
            } catch (NamingException ne) {
                addLog(ERROR, "datasource initialisation error : source=" + source + " *** message=" + ne.getMessage());
            }
        }
    }
    
    /**
     * constructor : DBAccess direct jdbc access settings
     */
    public DBAccess(String driver, String url, String user, String password) {
        if (driver == null)
            addLog(ERROR, "DBAccess settings error : driver null");
        else if (url == null)
            addLog(ERROR, "DBAccess settings error : url null");
        else if (user == null)
            addLog(ERROR, "DBAccess settings error : user null");
        else if (password == null)
            addLog(ERROR, "DBAccess settings error : password null");
        else {
            addLog(INFO, "DBAccess settings : driver=" + driver + " url=" + url + " user=" + user + " password=" + password);
            this.driver = driver;
            this.url = url;
            this.user = user;
            this.password = password;
            isDirect = true;
        }
    }

    /**
     * constructor : DBAccess direct jdbc access settings & logging system initialisation
     */
    public DBAccess(String driver, String url, String user, String password, boolean isLog4jLogging) {
        setLoggingSystem(isLog4jLogging);
        if (driver == null)
            addLog(ERROR, "DBAccess settings error : driver null");
        else if (url == null)
            addLog(ERROR, "DBAccess settings error : url null");
        else if (user == null)
            addLog(ERROR, "DBAccess settings error : user null");
        else if (password == null)
            addLog(ERROR, "DBAccess settings error : password null");
        else {
            addLog(INFO, "DBAccess settings : driver=" + driver + " url=" + url + " user=" + user + " password=" + password);
            this.driver = driver;
            this.url = url;
            this.user = user;
            this.password = password;
            isDirect = true;
        }
    }
    
    /**
     * return a new connexion with AutoCommit setting
     */
    private Connection getConnection(boolean isAutoCommit) {
        if (dataSource == null) {
            addLog(ERROR, "getConnection error : dataSource null");
            return null;
        }
        Connection connection = null;
        try {
            connection = dataSource.getConnection();
            connection.setAutoCommit(isAutoCommit);
        } catch (SQLException se) {
            addLog(ERROR, "getConnection error : code=" + se.getErrorCode() + " *** message=" + se.getMessage());
        }
        return connection;
    }

    /**
     * return a new connexion with AutoCommit setting
     */
    private Connection getDirectConnection(boolean isAutoCommit) {
        Connection connection = null;
        try {
            Class.forName(driver);
            connection = DriverManager.getConnection(url, user, password);
            connection.setAutoCommit(isAutoCommit);
        } catch (SQLException se) {
            addLog(ERROR, "getDirectConnection error : code=" + se.getErrorCode() + " *** message=" + se.getMessage());
        } catch (Exception e) {
            addLog(ERROR, "getDirectConnection error : " + e.getMessage());
        }
        return connection;
    }

    /**
     * close a connexion
     */
    private void closeConnection(Connection connection) {
        if (connection == null) {
            addLog(ERROR, "closeConnection error : connection null");
            return;
        }
        try {
            connection.close();
        } catch (SQLException se) {
            addLog(ERROR, "closeConnection error : code=" + se.getErrorCode() + " *** message=" + se.getMessage());
        }
    }

    /**
     * return a new statement
     */
    private Statement createStatement(Connection connection) {
        if (connection == null) {
            addLog(ERROR, "createStatement error : connection null");
            return null;
        }
        Statement statement = null;
        try {
            statement = connection.createStatement();
        } catch (SQLException se) {
            addLog(ERROR, "createStatement error : code=" + se.getErrorCode() + " *** message=" + se.getMessage());
        }
        return statement;
    }

    /**
     * close a statement
     */
    private void closeStatement(Statement statement) {
        if (statement == null) {
            addLog(ERROR, "closeStatement error : statement null");
            return;
        }
        try {
            statement.close();
        } catch (SQLException se) {
            addLog(ERROR, "closeStatement error : code=" + se.getErrorCode() + " *** message=" + se.getMessage());
        }
    }

    /**
     * execute a SQL request and return a single ResultSet
     */
    private ResultSet executeQuery(Statement statement, String sql) {
        addLog(DEBUG, "executeQuery '" + sql + "'");
        if (statement == null) {
            addLog(ERROR, "executeQuery error : statement null");
            return null;
        }
        ResultSet resultSet = null;
        try {
            resultSet = statement.executeQuery(sql);
        } catch (SQLException se) {
            addLog(ERROR, "executeQuery error : sql=" + sql + " *** code=" + se.getErrorCode() + " *** message=" + se.getMessage());
        }
        return resultSet;
    }

    /**
     * execute a SQL request (INSERT, UPDATE, DELETE) and return a row count
     */
    private int executeUpdate(Statement statement, String sql, boolean log) {
        if (log)
            addLog(DEBUG, "executeUpdate '" + sql + "'");
        if (statement == null) {
            addLog(ERROR, "executeUpdate error : statement null");
            return -1;
        }
        int rowCount = -1;
        try {
            rowCount = statement.executeUpdate(sql);
        } catch (SQLException se) {
            addLog(ERROR, "executeUpdate error : sql=" + sql + " *** code=" + se.getErrorCode() + " *** message=" + se.getMessage());
        }
        return rowCount;
    }

    
    /**
     * execute a SQL requests Vector (INSERT, UPDATE, DELETE) and return a row
     */
    private int[] executeBatch(Statement statement, Vector sql) {
        if (statement == null) {
            addLog(ERROR, "executeBatch error : statement null");
            return null;
        }
        int[] rowCount = null;
        try {
            for (int i = 0; i < sql.size(); i++) {
                statement.addBatch((String) sql.elementAt(i));
                //addLog(DEBUG, "executeBatch '" + sql + "'");
            }
            rowCount = statement.executeBatch();
        } catch (SQLException se) {
            addLog(ERROR, "executeBatch error : code=" + se.getErrorCode() + " *** message=" + se.getMessage());
        }
        return rowCount;
    }

    /**
     * execute a SQL request (sql) and return a 2 keys Hashtable (hashtableResult) - return null when a database access error or a SQL error
     */
    // key=labels : not null String Vector constituted by the columns names
    // key=results : not null Hashtable Vector - 1 Hashtable = 1 ResultSet line
    // if there is no result : results.size()=0
    // if a field is null : record.get("label")=null
    // warning : if 2 columns names are similare only the first one values are saved columns names doubloons are visible in labels vector
    public Hashtable processQuery(String sql) {
        Hashtable hashtableResult = null;
        ResultSet rs = null;
        Statement statement = null;
        Connection connection = null;
        try {
            if (isDirect)
                connection = getDirectConnection(true);
            else
                connection = getConnection(true);
            statement = createStatement(connection);
            rs = executeQuery(statement, sql);
            if (rs != null) {
                hashtableResult = new Hashtable();
                Vector labels = new Vector();
                Vector results = new Vector();
                ResultSetMetaData meta = rs.getMetaData();
                for (int i = 1; i <= meta.getColumnCount(); i++)
                    labels.addElement(meta.getColumnName(i));
                Hashtable record;
                while (rs.next()) {
                    record = new Hashtable();
                    for (int i = 1; i <= meta.getColumnCount(); i++)
                        if (rs.getObject(meta.getColumnName(i)) != null)
                            record.put(meta.getColumnName(i), rs.getObject(meta.getColumnName(i)));
                    results.addElement(record);
                }
                hashtableResult.put("labels", labels);
                hashtableResult.put("results", results);
            }
        } catch (Exception e) {
            addLog(ERROR, "unexpected error in processQuery : " + e.getMessage());
            hashtableResult = null;
        } finally {
            try {
                rs.close();
            } catch (Exception e) {
                addLog(ERROR, "processQuery rs.close error : " + e.getMessage());
            }
            try {
                closeStatement(statement);
            } catch (Exception e) {
                addLog(ERROR, "processQuery closeStatement error : " + e.getMessage());
            }
            try {
                closeConnection(connection);
            } catch (Exception e) {
                addLog(ERROR, "processQuery closeConnection error : " + e.getMessage());
            }
        }
        return hashtableResult;
    }

    /**
     * execute a SQL request and return the processQuery results Vector - return null when a database access error or a SQL error occure
     */
    public Vector processSimpleQuery(String sql) {
        Vector vectorResult = null;
        ResultSet rs = null;
        Statement statement = null;
        Connection connection = null;
        try {
            if (isDirect)
                connection = getDirectConnection(true);
            else
                connection = getConnection(true);
            statement = createStatement(connection);
            rs = executeQuery(statement, sql);
            if (rs != null) {
                vectorResult = new Vector();
                ResultSetMetaData meta = rs.getMetaData();
                Hashtable record;
                while (rs.next()) {
                    record = new Hashtable();
                    for (int i = 1; i <= meta.getColumnCount(); i++)
                        if (rs.getObject(meta.getColumnName(i)) != null)
                            record.put(meta.getColumnName(i), rs.getObject(meta.getColumnName(i)));
                    vectorResult.addElement(record);
                }
            }
        } catch (Exception e) {
            addLog(ERROR, "unexpected error in processSimpleQuery : " + e.getMessage());
            vectorResult = null;
        } finally {
            try {
                rs.close();
            } catch (Exception e) {
                addLog(ERROR, "processSimpleQuery rs.close error : " + e.getMessage());
            }
            try {
                closeStatement(statement);
            } catch (Exception e) {
                addLog(ERROR, "processSimpleQuery closeStatement error : " + e.getMessage());
            }
            try {
                closeConnection(connection);
            } catch (Exception e) {
                addLog(ERROR, "processSimpleQuery closeConnection error : " + e.getMessage());
            }
        }
        return vectorResult;
    }

    /**
     * execute a SQL request (INSERT, UPDATE, DELETE) and return row count (return -1 when a database access error or a SQL error occure) -
     * if more than one update is made only the first one row count is returned (use processBatch instead !)
     */
    public int processUpdate(String sql) {
        int rowcount = -1;
        Statement statement = null;
        Connection connection = null;
        try {
            if (isDirect)
                connection = getDirectConnection(true);
            else
                connection = getConnection(true);
            statement = createStatement(connection);
            rowcount = executeUpdate(statement, sql, true);
        } catch (Exception e) {
            addLog(ERROR, "unexpected error in processUpdate : " + e.getMessage());
            rowcount = -1;
        } finally {
            try {
                closeStatement(statement);
            } catch (Exception e) {
                addLog(ERROR, "processUpdate closeStatement error : " + e.getMessage());
            }
            try {
                closeConnection(connection);
            } catch (Exception e) {
                addLog(ERROR, "processUpdate closeConnection error : " + e.getMessage());
            }
        }
        return rowcount;
    }

    /**
     * execute a SQL request (INSERT, UPDATE, DELETE) and return row count (return -1 when a database access error or a SQL error occure) -
     * if more than one update is made only the first one row count is returned (use processBatch instead !)
     */
    public int processUpdateNoLog(String sql) {
        int rowcount = -1;
        Statement statement = null;
        Connection connection = null;
        try {
            if (isDirect)
                connection = getDirectConnection(true);
            else
                connection = getConnection(true);
            statement = createStatement(connection);
            rowcount = executeUpdate(statement, sql, false);
        } catch (Exception e) {
            addLog(ERROR, "unexpected error in processUpdate : " + e.getMessage());
            rowcount = -1;
        } finally {
            try {
                closeStatement(statement);
            } catch (Exception e) {
                addLog(ERROR, "processUpdate closeStatement error : " + e.getMessage());
            }
            try {
                closeConnection(connection);
            } catch (Exception e) {
                addLog(ERROR, "processUpdate closeConnection error : " + e.getMessage());
            }
        }
        return rowcount;
    }
    
    /**
     * execute a SQL batch requests (INSERT, UPDATE, DELETE) and return row count (return null when a database access error or a SQL error
     * occure) - requests are comitted at the end of the process
     */
    public int[] processBatch(Vector sql) {
        int[] rowcount = null;
        Statement statement = null;
        Connection connection = null;
        try {
            if (isDirect)
                connection = getDirectConnection(false);
            else
                connection = getConnection(false);
            statement = createStatement(connection);
            rowcount = executeBatch(statement, sql);
        } catch (Exception e) {
            addLog(ERROR, "unexpected error in processBatch : " + e.getMessage());
            rowcount = null;
        } finally {
            try {
                closeStatement(statement);
            } catch (Exception e) {
                addLog(ERROR, "processBatch closeStatement error : " + e.getMessage());
            }
            try {
                closeConnection(connection);
            } catch (Exception e) {
                addLog(ERROR, "processBatch closeConnection error : " + e.getMessage());
            }
        }
        return rowcount;
    }

}